/**
 * 封装所有的sql语句
 */
module.exports = {
    users: {
        login: "select * from users where (userName=? or account=?) and `password`=?;",
        adminLogin: "select * from users where userName='admin' and `password`='123123';",
        register1: "select * from users where userName=? or account=?;",
        register2: "insert into users (userName,account,password) values(?,?,?);",
        userAll:"select * from users where userID = ?;",
        userRedact:"select * from users where userName=? or account=?;",
        userRedact2:"update users set userName=?,age=?,phone=?,account=?,password=?,gender=?,headImg=? where userID = ?;",

    },
    listen: {
        newSong: "select songName from songs  where newSongID = ? GROUP BY songName;",
        newSong2: "select * from songs right join singers on songs.singers = singers.singerID where newSongID = ? and songName = ?;",
        recommend: "select songName from songs where recommendID = ? GROUP BY songName;",
        recommend2: "select * from songs right join singers on songs.singers = singers.singerID where recommendID = ? and songName = ?;",
        calssify: "select songName,calssifyID from songs where calssifyID = ? GROUP BY songName;",
        calssify2: "select * from songs right join singers on songs.singers = singers.singerID where calssifyID = ? and songName = ?;",
        region: "select songName,regionID from songs where regionID = ? GROUP BY songName;",
        region2: "select * from songs right join singers on songs.singers = singers.singerID where regionID = ? and songName = ?;",
        dotSong: "select * from songs right join singers on songs.singers = singers.singerID where songsID = ?;",
        dotSinger: "select * from songs right join singers on songs.singers = singers.singerID where singerID = ?;",
        searchSong: "select songName from songs where songName like '%?%' GROUP BY songName;",
        searchSong2: "select * from songs right join singers on songs.singers = singers.singerID where songName = ?;",
        searchSinger: "select singerName from singers where singerName like '%?%' GROUP BY singerName;",
        searchSinger2: "select * from songs right join singers on songs.singers = singers.singerID where singerName = ?;",
        songLists: "select songListName from songLists group by songListName",
        songLists2: "select songListName,songListImg from songLists where songListName = ?",
        songLists3: "select * from songlists RIGHT JOIN users on songlists.uid = users.userID where songListName = ?",
        songLists4: "select * from songs right join singers on songs.singers = singers.singerID where songsID = ?;",
        songLists5:"select * from songListLikes where songList_uid=? and songListName=?;",
    },
    singers: {
        singers: "select * from singers;",
    },
    comments: {
        song: "select * from comments where song_sid=?;",
        songListName: "select * from comments where songList_name=?;",
        songWrite: "insers into comments (comment_uid,word,time,song_sid) value(?,?,?,?);",
        songListWrite: "insers into comments (comment_uid,word,time,songList_name) value(?,?,?,?);",
        songDelete: "delete from comments where comment_uid = ? and word = ? and time = ? and song_sid = ?;",
        songListDelete: "delete from comments where comment_uid = ? and word = ? and time = ? and songList_name = ?;",
    },
    likes: {
        songLike: "insert into likes value(null,?,?);",
        songLike2:"select * from likes where userID = ? and songID = ?",
        songListLike: "insert into songListLikes value(null,?,?);",
        songListLike2:"select * from songListLikes where songList_uid = ? and songListName = ?",
        songCancel: "delete from likes where songID = ? and userID = ?;",
        songListCancel: "delete from songListLikes where songList_uid = ? and songListName = ?;",
        songLikes: "select * from likes where userID = ?;",
        songLikes2: "select * from songs right join singers on songs.singers = singers.singerID where songs.songsID = ?;",
        songListLikes: "select * from songListLikes where songList_uid = ?;",
        songListLikes4: "select songListName,songListImg,uid from songlists where songListName = ? LIMIT 0, 1; ",
    },
    admin:{
        usersAll:"select * from users;",
        songsAll:"select * from songs;",
        songListsAll: "select songListName from songLists group by songListName",
        songListsAll2: "select songListName,songListImg,uid from songLists where songListName = ?",
        usersDelete:"delete from users where userID = ?;",
        usersDelete2:"delete from songLists where uid = ?;",
        userRedact:"select * from users where userID =?;",
        userRedact2:"update users set userName=?,age=?,phone=?,account=?,password=?,gender=?,headImg=? where userID = ?",
        userDot:"select * from users where userID = ?;",
        songListsDelete:"delete from songLists where songListName = ?;",
        songsDelete:"delete from songs where songsID = ?;",
        songsDelete_list:"delete from songLists where sid = ?;",
        songRedact:"select singerID from singers where sid=?",
        songRedact2:"update songs set songsID=?,songName=?,word=?,imgUrl=?,singers=?,url=?,songWord=?,regionID=?,calssifyID=?,newSongID=?,recommendID=?",
        songCommentDelete:"delete from songs where comment_uid=?,word=?,time=?,song_sid=?;",
        songListCommentDelete:"delete from songs where comment_uid=?,word=?,time=?,songListName=?;",
        songListRedact:"update songLists set sid=?,uid=?,songListName=?,songListImg=?;",
    }
}